package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.iris.live.services.common.DateFormatUtils;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.repositories.CrossDepartmentTacticsRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lusong on 2016/11/25
 */
@Repository
@Transactional//(readOnly = true)
public class CrossDepartmentTacticsRepositoryImpl extends BaseRepository implements CrossDepartmentTacticsRepository {
    @Override
   /* 获取有效线索数  推荐 在购 呼入 网络 主动集客*/
    public List<Object> findCountByPermitAndYMAndCarCode(String permit, String year, String month, String carCode, String groupType, String kind) {
        String[] kinds = kind.split(",");

        if (month != null && month.length() == 1) {
            month = "0" + month;
        }
        String date = year + "-" + month + "-01";
        StringBuffer sql = new StringBuffer(" " +
                "  SELECT COUNT(a.渠道 ) 有效线索数, a.渠道  as 渠道  ");

        if ("1".equals(groupType)) {
            sql.append(" , a.`所属销售顾问`  as 销售顾问\n ");
        }

        if ("2".equals(groupType)) {
            sql.append(" , a.`所属CDO`  as CDO\n ");
        }

        sql.append("  FROM \n" +
                "  `lsh导入名单` AS a \n" +
                "  WHERE \n" +
                "  a.`许可` =:permit  \n" +
                "  AND a.渠道 IS NOT NULL \n" +
                "  AND a.`线索创建日期` >= DATE_sub( date_sub( :date , INTERVAL extract(DAY FROM :date) - 1 DAY) , INTERVAL 2 MONTH) \n" +
                "  AND a.`线索创建日期` <= LAST_DAY( :date ) " +
                "  AND a.`是否有效线索`='是'  AND a.`是否线索`='是' ");

        for(int index = 0; index < kinds.length; index++) {
            if(index == 0) sql.append(" and (");
            else sql.append(" or ");

            sql.append(" a.渠道 =:kind").append("_").append(index);

            if(index == kinds.length - 1){
                sql.append(")");
            }
        }

        Map params = new HashMap();
        params.put("permit", permit);
        params.put("date", date);

        for(int index = 0; index < kinds.length; index++) {
            params.put("kind_" + index, kinds[index]);
        }

        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }
        if ("1".equals(groupType)) {
            sql.append(" AND a.`所属销售顾问` IS NOT NULL ");
            sql.append(" AND a.`所属销售顾问` <> '  ' ");
        }
        if ("2".equals(groupType)) {
            sql.append(" AND a.`所属CDO` IS NOT NULL ");
            sql.append(" AND a.`所属CDO` <> '  ' ");
        }

        sql.append(" GROUP BY  a.`渠道`  ");

        if (groupType != null && "1".equals(groupType)) {
            sql.append(", a.`所属销售顾问`  ");
        }
        if ("2".equals(groupType)) {
            sql.append(",  a.`所属CDO`");
        }

        return this.getList(sql.toString(), params);

    }

    @Override
   /* 获取周有效线索数  推荐 在购 呼入 网络 主动集客*/
    public List<Object> findCountByPermitAndYMAndCarCodeWeek(String permit, String beginDay, String endDay, String carCode, String groupType, String kind, Integer day, Integer days) {
        if(! StringUtils.isNullOrBlank(beginDay)){
            beginDay = DateFormatUtils.addSeperator(beginDay, "-");
        }

        if(! StringUtils.isNullOrBlank(endDay)){
            endDay = DateFormatUtils.addSeperator(endDay, "-");
        }

        String[] kinds = kind.split(",");

        StringBuffer sql = new StringBuffer(" " +
                " SELECT  SUM(线索数) as 有效线索数, a.渠道  as 渠道 ");
        if ("1".equals(groupType)) {
            sql.append(", a.销售顾问  ");
        }
        if ("2".equals(groupType)) {
            sql.append(" ,a.CDO  ");
        }
        sql.append(" from ( SELECT round(COUNT(a.渠道 )*" + day.toString() + "/" + days.toString() + " )  线索数, a.渠道  as 渠道 ");
        if ("1".equals(groupType)) {
            sql.append(",  a.`所属销售顾问`  as 销售顾问 \n ");
        }
        if ("2".equals(groupType)) {
            sql.append(",  a.`所属CDO`  as CDO \n ");
        }
        sql.append(" FROM  " +
                " `lsh导入名单` AS a  " +
                " WHERE  " +
                " a.`许可` =:permit " +
                " AND a.渠道 IS NOT NULL " +
                " AND a.`是否有效线索`='是'  AND a.`是否线索`='是'  ");

        for(int index = 0; index < kinds.length; index++) {
            if(index == 0) sql.append(" AND (");
            else sql.append(" OR ");

            sql.append(" a.渠道 =:kind").append("_").append(index);

            if(index == kinds.length - 1){
                sql.append(")");
            }
        }

        if ("1".equals(groupType)) {
            sql.append(" AND a.`所属销售顾问` IS NOT NULL ");
            sql.append(" AND a.`所属销售顾问` <> '  ' ");
        }
        if ("2".equals(groupType)) {
            sql.append(" AND a.`所属CDO` IS NOT NULL ");
            sql.append(" AND a.`所属CDO` <> '  ' ");
        }
        sql.append(" AND a.`线索创建日期` >= DATE_sub( date_sub( :beginDay , INTERVAL extract(DAY FROM :beginDay) - 1 DAY) , INTERVAL 2 MONTH)  " +
                " AND a.`线索创建日期` <= LAST_DAY( :beginDay ) ");

        sql.append(" GROUP BY a.渠道 ");
        if ("1".equals(groupType)) {
            sql.append(", a.`所属销售顾问`");
        }

        if ("2".equals(groupType)) {
            sql.append(", a.`所属CDO`");
        }

        sql.append(" UNION ALL" +
                " SELECT COUNT(a.渠道 )  有效线索数 , a.渠道  as 渠道");
        if ("1".equals(groupType)) {
            sql.append(",  a.`所属销售顾问`  as 销售顾问\n ");
        }
        if ("2".equals(groupType)) {
            sql.append(",  a.`所属CDO`  as CDO\n ");
        }
        sql.append(" FROM  `lsh导入名单` AS a  " +
                " WHERE  " +
                " a.`许可` =:permit " +
                " AND a.渠道 IS NOT NULL " +
                " AND a.`是否有效线索`='是'  AND a.`是否线索`='是'  ");

        for(int index = 0; index < kinds.length; index++) {
            if(index == 0) sql.append(" AND (");
            else sql.append(" OR ");

            sql.append(" a.渠道 =:kind").append("_").append(index);

            if(index == kinds.length - 1){
                sql.append(")");
            }
        }

        if ("1".equals(groupType)) {
            sql.append(" AND a.`所属销售顾问` IS NOT NULL ");
            sql.append(" AND a.`所属销售顾问` <> '  ' ");
        }
        if ("2".equals(groupType)) {
            sql.append(" AND a.`所属CDO` IS NOT NULL ");
            sql.append(" AND a.`所属CDO` <> '  ' ");
        }

        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
        }
        sql.append(" AND  a.`线索创建日期`>=:beginDay" +
                " AND  a.`线索创建日期`<=:endDay");

        sql.append(" GROUP BY a.渠道 ");
        if ("1".equals(groupType)) {
            sql.append(", a.`所属销售顾问`");
        }
        if ("2".equals(groupType)) {
            sql.append(", a.`所属CDO`");
        }
        sql.append(" ) AS a");

        sql.append(" GROUP BY a.渠道 ");

        if ("1".equals(groupType)) {
            sql.append(",  a.`销售顾问`");
        }
        if ("2".equals(groupType)) {
            sql.append(", a.`CDO`");
        }

        Map params = new HashMap();
        params.put("permit", permit);
        params.put("beginDay", beginDay);
        params.put("endDay", endDay);

        for(int index = 0; index < kinds.length; index++) {
            params.put("kind_" + index, kinds[index].trim());
        }

        if (carCode != null && !"".equals(carCode)) {
            params.put("carCode", carCode);
        }
        return this.getList(sql.toString(), params);
    }

    @Override
    public List<Object> findResultAboutPermitAndYMAndCarCode(String permit, String ym, String carCode, String type, String groupType) {
        String[] types = type.split(",");

        StringBuffer sb = new StringBuffer();
        sb.append(" select sum(a.有效线索数) 有效线索数,a.渠道 ");
        if ("1".equals(groupType)) {
            sb.append(" ,a.销售顾问 ");
        }
        sb.append(" from 线索统计 a ");
        sb.append(" where a.许可 =:permit ");
        sb.append(" and a.周期 =:ym ");
        for(int index = 0; index < types.length; index++) {
            if(index == 0) sb.append(" and (");
            else sb.append(" or ");

            sb.append(" a.渠道 =:type").append("_").append(index);

            if(index == types.length - 1){
                sb.append(")");
            }
        }

        if (carCode != null && !"".equals(carCode) && !"总计".equals(carCode)) {
            sb.append(" and a.车新编号 =:carCode ");
        }
        sb.append(" group by a.渠道 ");
        if ("1".equals(groupType)) {
            sb.append(" ,a.销售顾问 ");
        }

        Map params = new HashMap();
        params.put("permit", permit);
        params.put("ym", ym);

        for(int index = 0; index < types.length; index++) {
            params.put("type_" + index, types[index]);
        }

        if (carCode != null && !"".equals(carCode)) {
            params.put("carCode", carCode);
        }
        return this.getList(sb.toString(), params);
    }

    @Override
  /*  获取 周活跃线索数 和休眠线索数*/
    public List<Object> findResultAboutPermitAndYMAndCarCodeWeek(String permit, String ym, String carCode, String type, String groupType, Integer day, Integer days) {
        String[] types = type.split(",");

        StringBuffer sb = new StringBuffer();
        sb.append(" select a.渠道, round(sum(a.有效线索数)*" + day.toString() + "/" + days.toString() + ") 有效线索数 ");
        if ("1".equals(groupType)) {
            sb.append(" ,a.销售顾问 ");
        }
        sb.append(" from 线索统计 a ");
        sb.append(" where a.许可 =:permit ");
        sb.append(" and a.周期 =:ym ");
        if (carCode != null && !"".equals(carCode) && !"总计".equals(carCode)) {
            sb.append(" and a.车新编号 =:carCode ");
        }

        for(int index = 0; index < types.length; index++) {
            if(index == 0) sb.append(" and (");
            else sb.append(" or ");

            sb.append(" a.渠道 =:type").append("_").append(index);

            if(index == types.length - 1){
                sb.append(")");
            }
        }

        sb.append(" group by a.渠道 ");
        if ("1".equals(groupType)) {
            sb.append(" ,a.销售顾问 ");
        }

        Map params = new HashMap();
        params.put("permit", permit);
        params.put("ym", ym);

        for(int index = 0; index < types.length; index++) {
            params.put("type_" + index, types[index]);
        }

        if (carCode != null && !"".equals(carCode)) {
            params.put("carCode", carCode);
        }
        return this.getList(sb.toString(), params);
    }

    @Override
   /* 获取试驾数*/
    public List getTestDrive(String permit, int year, int startMonth, int endMonth, String carCode, String groupType) {
        StringBuffer sql = new StringBuffer("  " +
                "  SELECT count(DISTINCT 顾客编号) AS 试驾数, 许可,a.`销售顾问`   " +
                "  FROM 顾客进店记录表 a   " +
                "  WHERE 是否试驾 = '是'  " +
                "  AND a.`许可`=:permit " +
                "  AND MONTH ( a.`进店日期`) >:startMonth   " +
                "  AND MONTH (a.`进店日期`) <=:endMonth " +
                "  AND YEAR(a.`进店日期`) =:year ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("year", year);
        params.put("startMonth", startMonth);
        params.put("endMonth", endMonth);
        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }
        if (groupType != null && "1".equals(groupType)) {
            sql.append("GROUP BY A.`销售顾问`");
        }
        return this.getList(sql.toString(), params);
    }


    @Override
   /* 获取销售顾问分组*/
    public List getSalesGroup(String permit) {
        Map params = Maps.newHashMap();
        params.put("permit", permit);
        StringBuffer sql = new StringBuffer("" +
                "   SELECT a.`组名`,a.`销售顾问`  FROM `销售顾问分组` AS a WHERE a.`许可` =:permit  AND `组名` <> ' ' ");
        return this.getList(sql.toString(), params);
    }

    @Override
   /* 获取交车数*/
    public List getpayCar(String permit, String year, String month, String carCode, String groupType, String beginDay, String endDay) {
        StringBuffer sql = new StringBuffer("  " +
                "  SELECT  COUNT(DISTINCT a.`初始报价单号`)  AS 交车数 ,a.`销售顾问`  " +
                " FROM`报价单表` AS a " +
                " JOIN  ( SELECT  *,MAX(aa.`报价编号`) AS `单号`  FROM  `报价单表` AS aa  group by `初始报价单号` )AS x ON x.`初始报价单号`= a.`初始报价单号` " +
                " JOIN `报价交车表` AS jc ON a.`交车`=jc.`编号` " +
                " WHERE a.`报价编号`=x.`单号` " +
                " AND a.`许可`=:permit " +
                " AND jc.`交车日期` IS NOT NULL ");
        Map params = new HashMap();
        params.put("permit", permit);
        if (month != null && !"".equals(month)) {
            sql.append(" AND  MONTH(jc.`交车日期`)=:month ");
            params.put("month", month);
        }
        if (year != null && !"".equals(year)) {
            sql.append(" AND  YEAR(jc.`交车日期`)=:year  ");
            params.put("year", year);
        }
        if (beginDay != null && !"".equals(beginDay)) {
            sql.append(" AND  DATE_FORMAT(jc.`交车日期`,'%Y%m%d')>=:beginDay  ");
            params.put("beginDay", beginDay);
        }
        if (endDay != null && !"".equals(endDay)) {
            sql.append(" AND  DATE_FORMAT(jc.`交车日期`,'%Y%m%d')<=:endDay  ");
            params.put("endDay", endDay);
        }

        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }
        if (groupType != null && "1".equals(groupType)) {
            sql.append("GROUP BY A.`销售顾问`");
        }
        return this.getList(sql.toString(), params);
    }

    @Override
  /* 获取订单数*/
    public List getOrdersCount(String permit, String year, String month, String carCode, String groupType, String beginDay, String endDay) {

        StringBuffer sql = new StringBuffer("  " +
                "  SELECT  COUNT(DISTINCT bj.`初始报价单号`) 订单数 , bj.`销售顾问` " +
                "  FROM  `lsh车型`  AS cx " +
                "  JOIN `报价单表`  AS  bj ON  cx.`车型编号`=bj.`车型编号`  " +
                "  JOIN  `报价订单表` AS  bjd ON  bj.`订单编号`=bjd.`订单编号`   " +
                "  WHERE  bj.`许可`=:permit  " +
                "  AND  bjd.`订单状态`<> '退订'  " +
                "  AND bjd.`订单号` IS NOT NULL " +
                "  AND  bjd.订金类型= '订金' " +
                "  AND  bjd.`订单状态`<> '' " +
                " AND  bjd.`订单状态`NOT LIKE '%意向订单%'");
        Map params = new HashMap();
        params.put("permit", permit);
        if (month != null && !"".equals(month)) {
            sql.append(" AND  MONTH(bjd.`订单签署日期`)=:month ");
            params.put("month", month);
        }
        if (year != null && !"".equals(year)) {
            sql.append(" AND  YEAR(bjd.`订单签署日期`)=:year  ");
            params.put("year", year);
        }
        if (beginDay != null && !"".equals(beginDay)) {
            sql.append(" AND  DATE_FORMAT(bjd.`订单签署日期`,'%Y%m%d')>=:beginDay  ");
            params.put("beginDay", beginDay);
        }
        if (endDay != null && !"".equals(endDay)) {
            sql.append(" AND  DATE_FORMAT(bjd.`订单签署日期`,'%Y%m%d')<=:endDay  ");
            params.put("endDay", endDay);
        }
        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }
        if (groupType != null && "1".equals(groupType)) {
            sql.append("GROUP BY bj.`销售顾问`");
        }
        return this.getList(sql.toString(), params);
    }

    @Override
/*获取报价数*/
    public List getQuoteCount(String permit, String year, String month, String carCode, String groupType, String beginDay, String endDay) {
        StringBuffer sql = new StringBuffer("  " +
                " select count(distinct 顾客编号) as 报价数, 许可 ,a.`销售顾问`  " +
                " from 顾客进店记录表 a   " +
                " join LSH车型 b on a.意向车型A = b.车型编号   " +
                " where 是否报价 = '是'  " +
                " and a.许可=:permit ");
        Map params = new HashMap();
        params.put("permit", permit);
        if (month != null && !"".equals(month)) {
            sql.append(" AND  MONTH(a.`进店日期`)=:month ");
            params.put("month", month);
        }
        if (year != null && !"".equals(year)) {
            sql.append(" AND  YEAR(a.`进店日期`)=:year  ");
            params.put("year", year);
        }
        if (beginDay != null && !"".equals(beginDay)) {
            sql.append(" AND  DATE_FORMAT(a.`进店日期`,'%Y%m%d')>=:beginDay  ");
            params.put("beginDay", beginDay);
        }
        if (endDay != null && !"".equals(endDay)) {
            sql.append(" AND  DATE_FORMAT(a.`进店日期`,'%Y%m%d')<=:endDay  ");
            params.put("endDay", endDay);
        }
        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }
        if (groupType != null && "1".equals(groupType)) {
            sql.append("GROUP BY A.`销售顾问`");
        }
        return this.getList(sql.toString(), params);
    }

    @Override
   /* 获取散客客流数*/
    public List getIndTraveler(String permit, String month_Begin_Date, String month_End_Date, String carCode, String groupType, String beginDay, String endDay) {
        if(! StringUtils.isNullOrBlank(beginDay)){
            beginDay = DateFormatUtils.addSeperator(beginDay, "-");
        }

        if(! StringUtils.isNullOrBlank(endDay)){
            endDay = DateFormatUtils.addSeperator(endDay, "-");
        }

        StringBuffer sql = new StringBuffer("  " +
                "  SELECT count(顾客编号) AS 散客客流数, " +
                "         SUM(IF(a.`是否留档`='是' AND a.`客户级别` <> 'E:六个月以上订单' AND a.意向车型A IS NOT NULL, 1, 0)) 散客留档数, " +
                "         SUM(IF(是否试驾 = '是', 1, 0)) 首次进店试驾数, " +
                "         许可 ,a.`销售顾问`   " +
                "  FROM 顾客进店记录表 a   " +
                "  WHERE a.`顾客来源` = '散客'  " +
                "  AND  a.`是否首次到店`='是' " +
                "  AND a.`许可`=:permit ");

        Map params = new HashMap();
        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(month_Begin_Date) && !Strings.isNullOrEmpty(month_End_Date)) {
            sql.append(" AND  a.`进店日期` BETWEEN :month_Begin_Date AND :month_End_Date ");
            params.put("month_Begin_Date", month_Begin_Date);
            params.put("month_End_Date", month_End_Date);
        }
        if (beginDay != null && !"".equals(beginDay)) {
            sql.append(" AND  a.`进店日期`>=:beginDay  ");
            params.put("beginDay", beginDay);
        }
        if (endDay != null && !"".equals(endDay)) {
            sql.append(" AND  a.`进店日期`<=:endDay  ");
            params.put("endDay", endDay);
        }
        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }
        if (groupType != null && "1".equals(groupType)) {
            sql.append("GROUP BY A.`销售顾问`");
        }
        return this.getList(sql.toString(), params);
    }


    @Override
   /* 获取各种进店线索数*/
    public List getEnterShopCount(String permit, String month_Begin_Date, String month_End_Date, String kind, String carCode, String groupType, String beginDay, String endDay) {
        String[] kinds = kind.split(",");
        if(! StringUtils.isNullOrBlank(beginDay)){
            beginDay = DateFormatUtils.addSeperator(beginDay, "-");
        }

        if(! StringUtils.isNullOrBlank(endDay)){
            endDay = DateFormatUtils.addSeperator(endDay, "-");
        }

        StringBuffer sql = new StringBuffer("SELECT COUNT(distinct concat(a.顾客编号))  进店线索数, a.许可,a.`销售顾问` ");
        if(kinds[0].equals("活跃")){
            sql.append(",  a.`顾客再次来源` 顾客来源");
        }else{
            sql.append(", a.`顾客来源` 顾客来源");
        }
        sql.append("  FROM 顾客进店记录表 a   " +
                "  join LSH车型 b on a.意向车型A = b.车型编号  " +
                "  WHERE a.许可=:permit" +
                "  AND ( a.`到店目的` = '看车比价' OR a.`到店目的` = '订单' OR ( a.`到店目的` = '交车' AND a.`是否订单` = '是' )) ");

        //bug 6413 活跃休眠线索数 取 顾客进店记录表.顾客再次来源 统计 -- 路松 2017-09-13
        for(int index = 0; index < kinds.length; index++) {
            if(index == 0) sql.append(" and (");
            else sql.append(" or ");

            if ("活跃".equals(kinds[index]) || "休眠".equals(kinds[index])) {
                sql.append("  a.`顾客再次来源`=:kind").append("_").append(index);
            } else {
                sql.append("  a.`顾客来源`=:kind").append("_").append(index);
            }

            if(index == kinds.length - 1){
                sql.append(")");
            }
        }

        sql.append("  AND a.顾客编号 is not null ");

        Map params = new HashMap();
        for(int index = 0; index < kinds.length; index++) {
            params.put("kind_" + index, kinds[index]);
        }

        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(month_Begin_Date) && !Strings.isNullOrEmpty(month_End_Date)) {
            sql.append(" AND  a.`进店日期` BETWEEN :month_Begin_Date AND :month_End_Date ");
            params.put("month_Begin_Date", month_Begin_Date);
            params.put("month_End_Date", month_End_Date);
        }
        if (beginDay != null && !"".equals(beginDay)) {
            sql.append(" AND  a.`进店日期`>=:beginDay  ");
            params.put("beginDay", beginDay);
        }
        if (endDay != null && !"".equals(endDay)) {
            sql.append(" AND  a.`进店日期`<=:endDay  ");
            params.put("endDay", endDay);
        }
        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }

        sql.append(" GROUP BY 顾客来源");
        if (groupType != null && "1".equals(groupType)) {
            sql.append(",A.`销售顾问`");
        }
        return this.getList(sql.toString(), params);
    }

    @Override
   /* 获取各种属于CDO的进店线索数*/
    public List getEnterShopCountCDO(String permit, String month_Begin_Date, String month_End_Date, String kind, String carCode, String groupType, String beginDay, String endDay) {
        String[] kinds = kind.split(",");

        if(! StringUtils.isNullOrBlank(beginDay)){
            beginDay = DateFormatUtils.addSeperator(beginDay, "-");
        }

        if(! StringUtils.isNullOrBlank(endDay)){
            endDay = DateFormatUtils.addSeperator(endDay, "-");
        }

        StringBuffer sql = new StringBuffer("  SELECT a.顾客来源, \n" +
                "COUNT( DISTINCT concat(a.顾客编号))  进店线索数,\n" +
                "a.许可,\n" +
                "d.`所属CDO` as CDO    \n" +
                "FROM\n" +
                "顾客进店记录表 a\n" +
                "JOIN LSH车型 b ON a.意向车型A = b.车型编号\n" +
                "JOIN `顾客记录汇总` c ON a.`顾客编号`=c.`顾客编号`\n" +
                "JOIN `lsh导入名单` d ON c.`顾客编号`=d.`顾客编号`\n" +
                "WHERE\n" +
                "a.许可 =:permit \n" +
                "AND ( a.`到店目的` = '看车比价' OR a.`到店目的` = '订单' OR ( a.`到店目的` = '交车' AND a.`是否订单` = '是' ))\n" +
                "AND a.顾客编号 IS NOT NULL \n" +
                "AND d.所属CDO IS NOT  NULL ");
        for(int index = 0; index < kinds.length; index++) {
            if(index == 0) sql.append(" and (");
            else sql.append(" or ");
            sql.append("  a.`顾客来源`=:kind").append("_").append(index);
            if(index == kinds.length - 1){
                sql.append(")");
            }
        }

        Map params = new HashMap();
        for(int index = 0; index < kinds.length; index++) {
            params.put("kind_" + index, kinds[index]);
        }

        params.put("permit", permit);
        if (!Strings.isNullOrEmpty(month_Begin_Date) && !Strings.isNullOrEmpty(month_End_Date)) {
            sql.append(" AND  a.`进店日期` BETWEEN :month_Begin_Date AND :month_End_Date ");
            params.put("month_Begin_Date", month_Begin_Date);
            params.put("month_End_Date", month_End_Date);
        }
        if (beginDay != null && !"".equals(beginDay)) {
            sql.append(" AND  a.`进店日期`>=:beginDay  ");
            params.put("beginDay", beginDay);
        }
        if (endDay != null && !"".equals(endDay)) {
            sql.append(" AND  a.`进店日期`<=:endDay  ");
            params.put("endDay", endDay);
        }
        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }

        sql.append(" GROUP BY a.顾客来源 ");
        if (groupType != null && "2".equals(groupType)) {
            sql.append(",d.`所属CDO` ");
        }
        return this.getList(sql.toString(), params);
    }

    @Override
   /* 获取本月目标值数据*/
    public List getAimValue(String year, String month, String permit) {
        StringBuffer sql = new StringBuffer("  " +
                "  SELECT *FROM  `月度设定目标` AS  a WHERE  a.`许可`=:permit  AND a.`月度`=:yue ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("yue", year + month);
        return this.getList(sql.toString(), params);
    }


    public int findOrderCountAboutWingAndCusAndShow(String permit, String yearMonth, String type) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  COUNT(DISTINCT   bjd.`订单号` ) 订单数   ");
        sb.append(" FROM  `lsh车型`  AS cx  ");
        sb.append(" JOIN `报价单表`  AS  bj ON  cx.`车型编号`=bj.`车型编号`   ");
        sb.append(" JOIN  `报价订单表` AS  bjd ON  bj.`订单编号`=bjd.`订单编号`    ");
        sb.append(" WHERE  bj.`许可`=:permit   ");
        sb.append(" AND  bjd.`订单状态`<> '退订'   ");
        sb.append(" AND bjd.`订单号` IS NOT NULL  ");
        sb.append(" AND  bjd.订金类型= '订金'  ");
        sb.append(" AND CONCAT(YEAR(bjd.`订单签署日期`),MONTH(bjd.`订单签署日期`)) =:yearMonth ");
        if ("大客户".equals(type)) {
            sb.append(" AND bjd.`销售类型` = 'F:大客户' ");
        } else if ("展厅".equals(type)) {
            sb.append(" AND bjd.`销售类型` <> 'F:大客户' ");
        }
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("yearMonth", yearMonth);
        return this.getCount(sb.toString(), params);
    }

    public int findTeslaCountAboutWingAndCusAndShow(String permit, String yearMonth, String type) {
        StringBuffer sb = new StringBuffer();
        sb.append("  SELECT  COUNT(DISTINCT a.`初始报价单号`)  AS 交车数    ");
        sb.append("  FROM`报价单表` AS a   ");
        sb.append("  JOIN `报价交车表` AS jc ON a.`交车`=jc.`编号`   ");
        sb.append("  JOIN  `报价订单表` AS  bjd ON  a.`订单编号`=bjd.`订单编号` ");
        sb.append("  WHERE  ");
        sb.append("  a.`许可`=:permit  ");
        sb.append("  AND a.`是否还原点`='是'  ");
        sb.append("  AND a.报价编号=(SELECT MAX(`报价编号`) FROM `报价单表` aa WHERE aa.`初始报价单号`=a.`初始报价单号`)  ");
        sb.append("  AND jc.`交车日期` IS NOT NULL   ");
        sb.append("  AND  CONCAT(YEAR(jc.`交车日期`),MONTH(jc.`交车日期`))=:yearMonth   ");
        if ("大客户".equals(type)) {
            sb.append(" AND bjd.`销售类型` = 'F:大客户' ");
        } else if ("展厅".equals(type)) {
            sb.append(" AND bjd.`销售类型` <> 'F:大客户' ");
        }
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("yearMonth", yearMonth);
        return this.getCount(sb.toString(), params);
    }

    public List<?> findUserInfoByPermit(String permit) {
        StringBuffer sb = new StringBuffer(" select a.用户名,a.人员姓名,a.角色,a.删除状态 from 用户 a where a.许可 =:permit ");
        Map params = new HashMap();
        params.put("permit", permit);
        return this.getList(sb.toString(), params);
    }


    public List<?> getAlreadyReceptionUser(String permit, String yearMonth) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select a.`销售顾问` from `顾客进店记录表` a  ");
        sb.append(" where a.`许可` =:permit and date_format(a.`进店日期`,'%Y%m') =:yearMonth GROUP BY a.`销售顾问` ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("yearMonth", yearMonth);
        return this.getList(sb.toString(), params);
    }

    public List<?> findWeekDepartmentTarget(String permit, String yearMonth, String carCode, String week, Double rate) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select ");
        sb.append(" 	a.`许可`, ");
        sb.append(" 	a.`年月`, ");
        sb.append(" 	a.`车型`, ");
        sb.append(" 	a.`部门`, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`主动集客线索数` - b.`主动集客线索数` > 0 THEN ROUND((a.`主动集客线索数` - b.`主动集客线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 主动集客线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`主动集客进店线索数` - b.`主动集客进店线索数` > 0 THEN ROUND((a.`主动集客进店线索数` - b.`主动集客进店线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 主动集客进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`休眠线索数` - b.`休眠线索数` > 0 THEN ROUND((a.`休眠线索数` - b.`休眠线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 休眠线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`休眠进店线索数` - b.`休眠进店线索数` > 0 THEN ROUND((a.`休眠进店线索数` - b.`休眠进店线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 休眠进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`再购线索数` - b.`再购线索数` > 0 THEN ROUND((a.`再购线索数` - b.`再购线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 再购线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`再购进店线索数` - b.`再购进店线索数` > 0 THEN ROUND((a.`再购进店线索数` - b.`再购进店线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 再购进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`呼入线索数` - b.`呼入线索数` > 0 THEN ROUND((a.`呼入线索数` - b.`呼入线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 呼入线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`呼入进店线索数` - b.`呼入进店线索数` > 0 THEN ROUND((a.`呼入进店线索数` - b.`呼入进店线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 呼入进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`推荐线索数` - b.`推荐线索数` > 0 THEN ROUND((a.`推荐线索数` - b.`推荐线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 推荐线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`推荐进店线索数` - b.`推荐进店线索数` > 0 THEN ROUND((a.`推荐进店线索数` - b.`推荐进店线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 推荐进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`散客线索数` - b.`散客线索数` > 0 THEN ROUND((a.`散客线索数` - b.`散客线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 散客线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`散客进店线索数` - b.`散客进店线索数` > 0 THEN ROUND((a.`散客进店线索数` - b.`散客进店线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 散客进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`活跃线索数` - b.`活跃线索数` > 0 THEN ROUND((a.`活跃线索数` - b.`活跃线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 活跃线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`活跃进店线索数` - b.`活跃进店线索数` > 0 THEN ROUND((a.`活跃进店线索数` - b.`活跃进店线索数`) * :rate) ");
        sb.append(" 		ELSE 0 END 活跃进店线索数 ");
        sb.append(" FROM `每月各责任部门目标` a  ");
        sb.append(" JOIN ( ");
        sb.append(" 	select ");
        sb.append(" 		a.`部门`, ");
        sb.append(" 		SUM(a.`主动集客线索数`) 主动集客线索数, ");
        sb.append(" 		SUM(a.`主动集客进店线索数`) 主动集客进店线索数 ,  ");
        sb.append(" 		SUM(a.`休眠线索数`) 休眠线索数,  ");
        sb.append(" 		SUM(a.`休眠进店线索数`) 休眠进店线索数,  ");
        sb.append(" 		SUM(a.`再购线索数`) 再购线索数,  ");
        sb.append(" 		SUM(a.`再购进店线索数`) 再购进店线索数,  ");
        sb.append(" 		SUM(a.`呼入线索数`) 呼入线索数,  ");
        sb.append(" 		SUM(a.`呼入进店线索数`) 呼入进店线索数,  ");
        sb.append(" 		SUM(a.`推荐线索数`) 推荐线索数,  ");
        sb.append(" 		SUM(a.`推荐进店线索数`) 推荐进店线索数,  ");
        sb.append(" 		SUM(a.`散客线索数`) 散客线索数,  ");
        sb.append(" 		SUM(a.`散客进店线索数`) 散客进店线索数,  ");
        sb.append(" 		SUM(a.`活跃线索数`) 活跃线索数,  ");
        sb.append(" 		SUM(a.`活跃进店线索数`) 活跃进店线索数 ");
        sb.append(" 	from `每周各责任部门目标` a  ");
        sb.append(" 	where a.`许可` =:permit  ");
        sb.append(" 		and a.`车型` =:carCode  ");
        sb.append(" 		and a.`年月` =:yearMonth  ");
        sb.append(" 		and a.`第N周` <:week  ");
        sb.append(" 	GROUP BY a.`部门` ");
        sb.append(" ) b ON a.`部门` = b.`部门` ");
        sb.append(" WHERE a.`年月` =:yearMonth and a.`许可` =:permit and a.`车型` =:carCode ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("yearMonth", yearMonth);
        params.put("carCode", carCode);
        params.put("week", week);
        params.put("rate", rate);
        return this.getList(sb.toString(), params);
    }

    public List<?> findWeekSalesTarget(String permit, String yearMonth, String carCode, String week, Double rate) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select  ");
        sb.append(" 	a.`许可`,a.`年月`,a.`车型`,a.`销售顾问`, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`总线索数` - b.`总线索数` > 0 THEN ROUND((a.`总线索数` - b.`总线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 总线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`进店线索数` - b.`进店线索数` > 0 THEN ROUND((a.`进店线索数` - b.`进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`报价数` - b.`报价数` > 0 THEN ROUND((a.`报价数` - b.`报价数`) *:rate) ");
        sb.append(" 	ELSE 0 END 报价数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`交车数` - b.`交车数` > 0 THEN ROUND((a.`交车数` - b.`交车数`) *:rate) ");
        sb.append(" 	ELSE 0 END 交车数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`试驾数` - b.`试驾数` > 0 THEN ROUND((a.`试驾数` - b.`试驾数`) *:rate) ");
        sb.append(" 	ELSE 0 END 试驾数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`主动集客线索数` - b.`主动集客线索数` > 0 THEN ROUND((a.`主动集客线索数` - b.`主动集客线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 主动集客线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`主动集客进店线索数` - b.`主动集客进店线索数` > 0 THEN ROUND((a.`主动集客进店线索数` - b.`主动集客进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 主动集客进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`休眠线索数` - b.`休眠线索数` > 0 THEN ROUND((a.`休眠线索数` - b.`休眠线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 休眠线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`休眠进店线索数` - b.`休眠进店线索数` > 0 THEN ROUND((a.`休眠进店线索数` - b.`休眠进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 休眠进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`再购线索数` - b.`再购线索数` > 0 THEN ROUND((a.`再购线索数` - b.`再购线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 再购线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`再购进店线索数` - b.`再购进店线索数` > 0 THEN ROUND((a.`再购进店线索数` - b.`再购进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 再购进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`呼入线索数` - b.`呼入线索数` > 0 THEN ROUND((a.`呼入线索数` - b.`呼入线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 呼入线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`呼入进店线索数` - b.`呼入进店线索数` > 0 THEN ROUND((a.`呼入进店线索数` - b.`呼入进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 呼入进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`推荐线索数` - b.`推荐线索数` > 0 THEN ROUND((a.`推荐线索数` - b.`推荐线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 推荐线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`推荐进店线索数` - b.`推荐进店线索数` > 0 THEN ROUND((a.`推荐进店线索数` - b.`推荐进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 推荐进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`散客线索数` - b.`散客线索数` > 0 THEN ROUND((a.`散客线索数` - b.`散客线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 散客线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`散客进店线索数` - b.`散客进店线索数` > 0 THEN ROUND((a.`散客进店线索数` - b.`散客进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 散客进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`活跃线索数` - b.`活跃线索数` > 0 THEN ROUND((a.`活跃线索数` - b.`活跃线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 活跃线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`活跃进店线索数` - b.`活跃进店线索数` > 0 THEN ROUND((a.`活跃进店线索数` - b.`活跃进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 活跃进店线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`网络线索数` - b.`网络线索数` > 0 THEN ROUND((a.`网络线索数` - b.`网络线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 网络线索数, ");
        sb.append(" 	CASE ");
        sb.append(" 		WHEN a.`网络进店线索数` - b.`网络进店线索数` > 0 THEN ROUND((a.`网络进店线索数` - b.`网络进店线索数`) *:rate) ");
        sb.append(" 	ELSE 0 END 网络进店线索数 ");
        sb.append(" FROM `每月销售顾问目标` a ");
        sb.append("  LEFT join	(select  ");
        sb.append(" 		a.`销售顾问`, ");
        sb.append(" 		SUM(a.`总线索数`) 总线索数, ");
        sb.append(" 		SUM(a.`进店线索数`) 进店线索数, ");
        sb.append(" 		SUM(a.`报价数`) 报价数, ");
        sb.append(" 		SUM(a.`订单数`) 订单数, ");
        sb.append(" 		SUM(a.`交车数`) 交车数, ");
        sb.append(" 		SUM(a.`试驾数`) 试驾数, ");
        sb.append(" 		SUM(a.`主动集客线索数`) 主动集客线索数, ");
        sb.append(" 		SUM(a.`主动集客进店线索数`) 主动集客进店线索数, ");
        sb.append(" 		SUM(a.`休眠线索数`) 休眠线索数, ");
        sb.append(" 		SUM(a.`休眠进店线索数`) 休眠进店线索数, ");
        sb.append(" 		SUM(a.`再购线索数`) 再购线索数, ");
        sb.append(" 		SUM(a.`再购进店线索数`) 再购进店线索数, ");
        sb.append(" 		SUM(a.`呼入线索数`) 呼入线索数, ");
        sb.append(" 		SUM(a.`呼入进店线索数`) 呼入进店线索数, ");
        sb.append(" 		SUM(a.`推荐线索数`) 推荐线索数, ");
        sb.append(" 		SUM(a.`推荐进店线索数`) 推荐进店线索数, ");
        sb.append(" 		SUM(a.`散客线索数`) 散客线索数, ");
        sb.append(" 		SUM(a.`散客进店线索数`) 散客进店线索数, ");
        sb.append(" 		SUM(a.`活跃线索数`) 活跃线索数, ");
        sb.append(" 		SUM(a.`活跃进店线索数`) 活跃进店线索数, ");
        sb.append(" 		SUM(a.`网络线索数`) 网络线索数, ");
        sb.append(" 		SUM(a.`网络进店线索数`) 网络进店线索数 ");
        sb.append(" 	from `每周销售顾问目标` a where a.`许可` =:permit and a.`年月` =:yearMonth and a.`车型` =:carCode and a.`第N周` <:week GROUP BY a.`销售顾问` ");
        sb.append(" ) b on a.`销售顾问` = b.`销售顾问` ");
        sb.append(" WHERE  a.`许可` =:permit and a.`年月` =:yearMonth and a.`车型` =:carCode ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("yearMonth", yearMonth);
        params.put("carCode", carCode);
        params.put("week", week);
        params.put("rate", rate);
        return this.getList(sb.toString(), params);
    }

    public List findMaxWeekAboutDepartment(String permit, String yearMonth, String carCode) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select ifnull(max(a.`第N周`),0) 第N周 ");
        sb.append(" from 每周各责任部门目标 a ");
        sb.append(" where a.`许可` =:permit AND a.`年月` =:yearMonth and a.`车型` =:carCode ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("yearMonth", yearMonth);
        params.put("carCode", carCode);
        return this.getList(sb.toString(), params);
    }

    public List findMaxWeekAboutSales(String permit, String yearMonth, String carCode) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select ifnull(max(a.`第N周`),0) 第N周 ");
        sb.append(" from `每周销售顾问目标` a ");
        sb.append(" where a.`许可` =:permit AND a.`年月` =:yearMonth and a.`车型` =:carCode ");
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("yearMonth", yearMonth);
        params.put("carCode", carCode);
        return this.getList(sb.toString(), params);
    }

    public List getOrderCountOverWrite(String permit, String year, String month, String carCode, String groupType) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select if(sum(订单数) = '' or sum(订单数) is null, 0, sum(订单数)) 订单数,销售顾问 from ( " +
                " 	select  " +
                " 			CASE  " +
                " 			WHEN month(c.订单审批通过日期) = :month  and year(c.订单审批通过日期)  = :year  " +
                " 			AND  month(c.订单失销日期) = :month and year(c.订单失销日期)  = :year  " +
                " 			THEN 0  " +
                " 			WHEN month(c.订单审批通过日期) = :month and year(c.订单审批通过日期) =:year THEN 1  " +
                " 			WHEN month(c.订单失销日期) = :month  and  year(c.订单失销日期) = :year THEN -1  " +
                " 			END  " +
                " 		 as 订单数, " +
                " 		b.销售顾问 " +
                " 	 from  报价单表  b  " +
                " 	 join 报价订单表 c on b.订单编号 = c.订单编号  " +
                " 	 where ((month(c.订单审批通过日期) = :month  AND year(c.订单审批通过日期) = :year)  " +
                " 		 OR ((month(c.订单失销日期) = :month  AND year(c.订单失销日期) = :year)))  " +
                " 		AND b.许可 = :permit ");
        Map params = new HashMap();
        if (carCode != null && !"".equals(carCode)) {
            sb.append("  AND b.`车型编号` = :carCode ");
            params.put("carCode", carCode);
        }
        sb.append(" 	group by b.初始报价单号 ) a ");
        if (groupType != null && "1".equals(groupType)) {
            sb.append("GROUP BY a.`销售顾问`");
        }
        params.put("permit", permit);
        params.put("month", month);
        params.put("year", year);
        return this.getList(sb.toString(), params);
    }
    public List getOrderCountOverWriteWeek(String permit, String carCode, String groupType,String beginDay, String endDay) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select if(sum(订单数) = '' or sum(订单数) is null, 0, sum(订单数)) 订单数,销售顾问 from ( " +
                " 	select  " +
                " 		CASE  \n" +
                "WHEN DATE_FORMAT(c.订单审批通过日期,'%Y%m%d') >= DATE_FORMAT(:beginDay,'%Y%m%d') AND DATE_FORMAT(c.订单审批通过日期,'%Y%m%d') <= DATE_FORMAT(:endDay,'%Y%m%d')  AND   \n" +
                "c.订单失销日期 >= :beginDay AND c.订单失销日期 <= :endDay THEN 0  \n" +
                "WHEN DATE_FORMAT(c.订单审批通过日期,'%Y%m%d') >= DATE_FORMAT(:beginDay,'%Y%m%d') AND DATE_FORMAT(c.订单审批通过日期,'%Y%m%d') <= DATE_FORMAT(:endDay,'%Y%m%d') THEN 1  \n" +
                "WHEN c.订单失销日期 >= :beginDay AND c.订单失销日期 <= :endDay THEN -1  \n" +
                "END   as 订单数, " +
                " 		b.销售顾问 " +
                " 	 from  报价单表  b  " +
                " 	 join 报价订单表 c on b.订单编号 = c.订单编号  " +
                " 	 where (( DATE_FORMAT(c.订单审批通过日期,'%Y%m%d') >= DATE_FORMAT(:beginDay,'%Y%m%d') AND DATE_FORMAT(c.订单审批通过日期,'%Y%m%d') <= DATE_FORMAT(:endDay,'%Y%m%d') ) " +
                "   or (c.订单失销日期 >= :beginDay AND c.订单失销日期 <= :endDay ))  " +
                " 		AND b.许可 = :permit ");
        Map params = new HashMap();
        if (carCode != null && !"".equals(carCode)) {
            sb.append("  AND b.`车型编号` = :carCode ");
            params.put("carCode", carCode);
        }
        sb.append(" 	group by b.初始报价单号 ) a ");
        if (groupType != null && "1".equals(groupType)) {
            sb.append("GROUP BY a.`销售顾问`");
        }
        params.put("permit", permit);
        params.put("beginDay", beginDay);
        params.put("endDay", endDay);
        return this.getList(sb.toString(), params);
    }

    public List getQuoteCountOverWrite(String permit, String year, String month, String carCode, String groupType ,String beginDay, String endDay) {
        StringBuffer sql = new StringBuffer(" SELECT " +
                " 	if(SUM(报价数) = '' or SUM(报价数) is null, 0, SUM(报价数)) 报价数, " +
                " 	a.`销售顾问` " +
                " FROM ( " +
                " 	SELECT  " +
                " 		1 AS 报价数, " +
                " 		a.`销售顾问` " +
                " 	FROM 报价单表 a " +
                " 	WHERE a.许可 = :permit ");
        Map params = new HashMap();
        params.put("permit", permit);
        if (month != null && !"".equals(month)) {
            sql.append(" AND  MONTH(a.`报价时间`) = :month ");
            params.put("month", month);
        }
        if (year != null && !"".equals(year)) {
            sql.append(" AND  YEAR(a.`报价时间`) = :year  ");
            params.put("year", year);
        }
        if (beginDay != null && !"".equals(beginDay)) {
            sql.append(" AND  DATE_FORMAT(a.`报价时间`,'%Y%m%d')>=:beginDay  ");
            params.put("beginDay", beginDay);
        }
        if (endDay != null && !"".equals(endDay)) {
            sql.append(" AND  DATE_FORMAT(a.`报价时间`,'%Y%m%d')<=:endDay  ");
            params.put("endDay", endDay);
        }
        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号` = :carCode ");
            params.put("carCode", carCode);
        }
        sql.append(" group by a.初始报价单号 ) a ");
        if (groupType != null && "1".equals(groupType)) {
            sql.append("GROUP BY a.`销售顾问`");
        }
        return this.getList(sql.toString(), params);
    }

    public List getpayCarOverWrite(String permit, String month_Begin_Date, String month_End_Date, String carCode, String groupType ,String beginDay, String endDay) {
        StringBuffer sql = new StringBuffer(" SELECT " +
                " 	if(COUNT(1) = '' or COUNT(1) is null, 0, COUNT(1)) AS 交车数, " +
                " 	a.销售顾问 " +
                " FROM vw_retailed_quotation a " +
                " LEFT JOIN 报价交车表 b ON a.交车 = b.编号 " +
                " WHERE a.许可 = :permit ");
        Map params = new HashMap();
        params.put("permit", permit);

        if (beginDay != null && !"".equals(beginDay)) {
            sql.append(" AND  b.`交车日期`>=:beginDay  ");
            params.put("beginDay", beginDay);
        }
        if (endDay != null && !"".equals(endDay)) {
            sql.append(" AND  b.`交车日期`<=:endDay  ");
            params.put("endDay", endDay);
        }
        if (!Strings.isNullOrEmpty(month_Begin_Date) && !Strings.isNullOrEmpty(month_End_Date)) {
            sql.append(" AND  b.`交车日期` BETWEEN :month_Begin_Date AND :month_End_Date ");
            params.put("month_Begin_Date", month_Begin_Date);
            params.put("month_End_Date", month_End_Date);
        }
        if (carCode != null && !"".equals(carCode)) {
            sql.append("  AND a.`车型编号`=:carCode ");
            params.put("carCode", carCode);
        }
        if (groupType != null && "1".equals(groupType)) {
            sql.append("GROUP BY a.`销售顾问`");
        }
        return this.getList(sql.toString(), params);
    }

    /**
     * 线索数统计
     * @param permit
     * @param ym
     * @param groupType
     * @return
     */
    public List getClueNumOfMonth (String permit, String ym, String groupType) {
        StringBuffer sql = new StringBuffer();
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("ym", ym);
        if (!"2".equals(groupType)) {
            sql.append(" SELECT a.渠道, SUM(a.线索数) 有效线索数, a.销售顾问 ");
            sql.append("  FROM ( ");
            sql.append(" 	SELECT ");
            sql.append(" 		IF(a.`顾客状态` IN ('活跃','休眠'),a.`顾客状态`,a.`顾客渠道`) `渠道`, ");
            sql.append(" 		a.`销售顾问`, ");
            sql.append(" 		IFNULL(COUNT(1),0) `线索数` ");
            sql.append(" 	FROM `顾客记录汇总` a ");
            sql.append(" 	WHERE a.`许可` = :permit AND (a.`顾客状态` != '战败' OR a.`顾客状态` IS NULL) ");
            sql.append("    AND a.`意向车型A` IS NOT NULL ");
            sql.append(" 	GROUP BY `渠道`,a.`销售顾问` ");
            sql.append(" 	UNION ALL ");
            sql.append(" 	SELECT ");
            sql.append(" 		a.`渠道` `渠道`, ");
            sql.append(" 		a.`所属销售顾问` 销售顾问, ");
            sql.append(" 		IFNULL(COUNT(1),0) `线索数` ");
            sql.append(" 	FROM `lsh导入名单` a ");
            sql.append(" 	WHERE a.`许可` = :permit AND a.`是否线索` = '是' AND a.`是否有效线索` = '是' ");
            sql.append(" 	AND a.`线索创建日期`>= date_sub( date_sub( date_format(:ym, '%Y-%m-%d'), INTERVAL extract(DAY FROM :ym) - 1 DAY ), INTERVAL 2 MONTH ) ");
            sql.append("    AND a.`车型编号` IS NOT NULL ");
            sql.append(" 	AND NOT EXISTS( ");
            sql.append(" 		SELECT c.`电话号码` FROM `顾客记录汇总` c ");
            sql.append(" 		WHERE ( ");
            sql.append(" 			c.`顾客状态` <> '战败' OR ISNULL(c.`顾客状态`) ");
            sql.append(" 			OR (c.`顾客状态`='战败' AND a.`线索创建日期`> IFNULL(c.战败日期,'1970-01-01')) ");
            sql.append(" 		) AND c.`许可` = a.`许可` AND a.`电话号码` = c.`电话号码` ");
            sql.append(" 	) ");
            sql.append(" 	GROUP BY `渠道`,a.`所属销售顾问` ");
            sql.append(" ) a ");
            sql.append(" WHERE a.渠道 IS NOT NULL ");
            sql.append(" GROUP BY a.`渠道` ");
            if ("1".equals(groupType)) {
                sql.append(" , a.销售顾问 ");
            }
        } else {
            sql.append(" SELECT a.渠道, SUM(a.线索数) 有效线索数, a.CDO ");
            sql.append("  FROM ( ");
            sql.append(" 	SELECT ");
            sql.append(" 		a.`渠道` `渠道`, ");
            sql.append(" 		a.`所属CDO` CDO, ");
            sql.append(" 		IFNULL(COUNT(1),0) `线索数` ");
            sql.append(" 	FROM `lsh导入名单` a ");
            sql.append(" 	WHERE a.`许可` = :permit AND a.`是否线索` = '是' AND a.`是否有效线索` = '是' and a.所属CDO <> '' and a.所属CDO is not null ");
            sql.append(" 	AND a.`线索创建日期`>= date_sub( date_sub( date_format(:ym, '%Y-%m-%d'), INTERVAL extract(DAY FROM :ym) - 1 DAY ), INTERVAL 2 MONTH ) ");
            sql.append("    AND a.`车型编号` IS NOT NULL ");
            sql.append(" 	AND NOT EXISTS( ");
            sql.append(" 		SELECT c.`电话号码` FROM `顾客记录汇总` c ");
            sql.append(" 		WHERE ( ");
            sql.append(" 			c.`顾客状态` <> '战败' OR ISNULL(c.`顾客状态`) ");
            sql.append(" 			OR (c.`顾客状态`='战败' AND a.`线索创建日期`> IFNULL(c.战败日期,'1970-01-01')) ");
            sql.append(" 		) AND c.`许可` = a.`许可` AND a.`电话号码` = c.`电话号码` ");
            sql.append(" 	) ");
            sql.append(" 	GROUP BY `渠道`,a.`所属CDO` ");
            sql.append(" ) a ");
            sql.append(" WHERE a.渠道 IS NOT NULL ");
            sql.append(" GROUP BY a.`渠道` , a.CDO ");
        }
        return getList(sql.toString(), params);
    }

    /**
     * 进店线索数统计
     * @param permit
     * @param startDate
     * @param endDate
     * @param groupType
     * @return
     */
    @Override
    public List getToShopClueNum (String permit, String startDate, String endDate, String groupType) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT  ");
        sql.append(" 	`销售顾问`,`渠道`,  ");
        sql.append(" 	SUM(`展厅客流数`) `展厅客流数`,   ");
        sql.append(" 	SUM(`首次进店线索数`) `首次进店线索数`,   ");
        sql.append(" 	SUM(`再次进店线索数`) `再次进店线索数`,   ");
        sql.append(" 	SUM(`首次进店试乘试驾数`) `首次进店试乘试驾数`,   ");
        sql.append(" 	SUM(`再次进店试乘试驾数`) `再次进店试乘试驾数`   ");
        sql.append("FROM (   ");
        sql.append("	SELECT  ");
        sql.append("		a.销售顾问, ");
        sql.append("		a.`顾客来源` 渠道,  ");
        sql.append("		0 `展厅客流数`,  ");
        sql.append("		COUNT(1) `首次进店线索数`, ");
        sql.append("		0  `再次进店线索数`,  ");
        sql.append(" 		SUM(IFNULL(tDrive.试驾次数, IF(接待记录.`开始试驾时间` IS NULL,0,1))) `首次进店试乘试驾数`, ");
        sql.append("		0  `再次进店试乘试驾数`  ");
        sql.append("	FROM `顾客进店记录表` a  ");
        sql.append("	LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号` ");
        sql.append("	LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号` ");
        sql.append("    LEFT JOIN (SELECT 进店编号, COUNT(1) 试驾次数 FROM 顾客试驾记录 WHERE `试驾开始时间` >=  concat(:startDate,' 00:00:01')  AND `试驾开始时间` <=  concat(:endDate, ' 23:59:59') GROUP BY 进店编号 ) tDrive ON A.进店编号= tDrive.进店编号 ");
        sql.append("    LEFT JOIN 临时接待记录 接待记录 ON A.进店编号= 接待记录.进店编号 ");
        sql.append("	WHERE a.`进店日期` >=   :startDate  AND a.`进店日期` <=   :endDate  ");
        sql.append("	      AND a.许可 =  :permit  ");
        sql.append("	      AND a.`是否首次到店` = '是' ");
        sql.append("	      AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')  ");
        sql.append("	      AND IFNULL(`停留时间`, 0 ) > 0  ");
        sql.append("	      AND a.`顾客来源` IN ('散客','呼入','网络','主动集客','推荐','再购') ");
        sql.append("	      AND cust.`顾客姓名`!='' ");
        sql.append("	      AND cust.`电话号码`!='' ");
        sql.append("	      AND car.`车型`!='' ");
        sql.append("	      AND a.`客户级别` NOT LIKE 'E%' ");
        sql.append("	      AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))  ");
        sql.append("	GROUP BY 渠道 ,销售顾问  ");
        sql.append("	UNION ALL ");
        sql.append("	SELECT  ");
        sql.append("		a.销售顾问, ");
        sql.append("		a.`顾客再次来源` `渠道`, ");
        sql.append("		0 `展厅客流数`,  ");
        sql.append("		0 `首次进店线索数`,  ");
        sql.append("		COUNT(1) `再次进店线索数`, ");
        sql.append("		0 AS `首次进店试乘试驾数`,  ");
        sql.append("		0 AS `再次进店试乘试驾数`  ");
        sql.append("	FROM `顾客进店记录表` a , ");
        sql.append("	 (  ");
        sql.append("	 	SELECT MIN(a.`进店编号`) `进店编号` ");
        sql.append("	 	FROM `顾客进店记录表` a  ");
        sql.append("	    LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号` ");
        sql.append("	    LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号` ");
        sql.append("	 	WHERE a.`进店日期` >=  date_sub( :startDate , INTERVAL extract(DAY FROM :startDate) - 1 DAY)  AND a.`进店日期` <= LAST_DAY(:startDate)  ");
        sql.append("	      AND a.许可 =  :permit  ");
        sql.append("	      AND a.`是否首次到店` = '否' ");
        sql.append("	      AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')  ");
        sql.append("	      AND IFNULL(`停留时间`, 0 ) > 0  ");
        sql.append("	      AND a.`顾客再次来源` IN ('活跃','休眠') ");
        sql.append("	      AND cust.`顾客姓名`!='' ");
        sql.append("	      AND cust.`电话号码`!='' ");
        sql.append("	      AND car.`车型`!='' ");
        sql.append("	      AND a.`客户级别` NOT LIKE 'E%' ");
        sql.append("	      AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是')) ");
        sql.append("	 	GROUP BY a.`顾客编号` ");
        sql.append("	 ) sub ");
        sql.append("	 WHERE a.`进店编号` = sub.进店编号 ");
        sql.append("	 AND a.`进店日期` >= :startDate AND a.`进店日期` <= :endDate ");
        sql.append("	GROUP BY 渠道,销售顾问  ");
        sql.append(" 	UNION ALL  ");
        sql.append(" 	SELECT  ");
        sql.append(" 		`销售顾问`, ");
        sql.append("		`顾客来源` `渠道`,  ");
        sql.append(" 		COUNT(1) `展厅客流数`,  ");
        sql.append(" 		0 `首次进店线索数`,  ");
        sql.append(" 		0 `再次进店线索数`,  ");
        sql.append(" 		0 `首次进店试乘试驾数`,  ");
        sql.append(" 		0 `再次进店试乘试驾数`  ");
        sql.append(" 	FROM   `顾客进店记录表` a    ");
        sql.append(" 	WHERE a.许可 =  :permit AND a.`进店日期`>=  :startDate  AND a.`进店日期`<=  :endDate  ");
        sql.append(" 	AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')  ");
        sql.append("	AND IFNULL(`停留时间`, 0) > 0  ");
        sql.append("	AND a.`顾客来源` = '散客' ");
        sql.append("	AND `是否首次到店` = '是'  ");
        sql.append(" 	AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))   ");
        sql.append(" 	GROUP BY 渠道,销售顾问  ");
        sql.append(" ) aa  ");
        sql.append("GROUP BY 渠道 ");
        if ("1".equals(groupType)) {
            sql.append("    ,销售顾问 ");
        }
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        return getList(sql.toString(), params);
    }

    /**
     * 销售漏斗数据统计
     *
     * @param permit
     * @param startDate
     * @param endDate
     * @param groupType
     * @return
     */
    public List getOtherClueNum (String permit, String startDate, String endDate, String groupType) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT  ");
        sql.append(" 	销售顾问,SUM(首次试驾数) 首次试驾数,SUM(报价数) 报价数,SUM(订单数) 订单数,SUM(交车数) 交车数 ");
        sql.append(" FROM ( ");
        sql.append(" 	SELECT  ");
        sql.append(" 		a.销售顾问, ");
        sql.append(" 		SUM(IFNULL(tDrive.试驾次数, IF(接待记录.`开始试驾时间` IS NULL,0,1))) `首次试驾数`, ");
        sql.append(" 		0 `报价数`, ");
        sql.append(" 		0 `订单数`, ");
        sql.append(" 		0 `交车数` ");
        sql.append(" 	FROM `顾客进店记录表` a  ");
        sql.append(" 	LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号` ");
        sql.append(" 	LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号` ");
        sql.append("    LEFT JOIN (SELECT 进店编号, COUNT(1) 试驾次数 FROM 顾客试驾记录 WHERE `试驾开始时间` >=  concat(:startDate,' 00:00:01')  AND `试驾开始时间` <=  concat(:endDate, ' 23:59:59') GROUP BY 进店编号 ) tDrive ON A.进店编号= tDrive.进店编号 ");
        sql.append("    LEFT JOIN 临时接待记录 接待记录 ON A.进店编号= 接待记录.进店编号 ");
        sql.append(" 	WHERE a.`进店日期` >=   :startDate  AND a.`进店日期` <=   :endDate  ");
        sql.append(" 	      AND a.许可 =  :permit  ");
        sql.append(" 	      AND a.`是否首次到店` = '是' ");
        sql.append(" 	      AND LEFT(a.到店时间,2) NOT IN ('00','01','02','03')  ");
        sql.append(" 	      AND IFNULL(`停留时间`, 0 ) > 0  ");
        sql.append(" 	      AND a.`顾客来源` IN ('散客','呼入','网络','主动集客','推荐','再购') ");
        sql.append(" 	      AND cust.`顾客姓名`!='' ");
        sql.append(" 	      AND cust.`电话号码`!='' ");
        sql.append(" 	      AND car.`车型`!='' ");
        sql.append(" 	      AND a.`客户级别` NOT LIKE 'E%' ");
        sql.append(" 	      AND(a.`到店目的` IN('看车比价','订单') OR ( a.`到店目的` IN('办手续' , '交车') AND a.`是否订单` = '是'))  ");
        sql.append(" 	GROUP BY 销售顾问  ");
        sql.append(" 	UNION ALL ");
        sql.append("	 SELECT  ");
        sql.append("	 	a.销售顾问, ");
        sql.append("	 	0 `首次试驾数`, ");
        sql.append("	 	COUNT(1)  `报价数`, ");
        sql.append("	 	0 `订单数`, ");
        sql.append("	 	0 `交车数` ");
        sql.append("	 FROM 顾客进店记录表 a, ");
        sql.append("	 (  ");
        sql.append("	 	SELECT MIN(a.`进店编号`) `进店编号` ");
        sql.append("	 	FROM `顾客进店记录表` a  ");
        sql.append("	 	LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号` ");
        sql.append("	 	LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号` ");
        sql.append("	 	WHERE a.`进店日期` >=  date_sub( :startDate , INTERVAL extract(DAY FROM :startDate) - 1 DAY)  AND a.`进店日期` <= LAST_DAY(:startDate)  ");
        sql.append("	 	      AND a.许可 = :permit  ");
        sql.append("	 	      AND cust.`顾客姓名`!='' ");
        sql.append("	 	      AND cust.`电话号码`!='' ");
        sql.append("	 	      AND car.`车型`!='' ");
        sql.append("	 	      AND a.`客户级别` NOT LIKE 'E%' ");
        sql.append("	 	      AND a.`是否报价` = '是' ");
        sql.append("	 	GROUP BY a.`顾客编号` ");
        sql.append("	 ) sub ");
        sql.append("	 WHERE a.`进店编号` = sub.进店编号 ");
        sql.append("	 AND a.`进店日期` >= :startDate AND a.`进店日期` <= :endDate ");
        sql.append("	 GROUP BY a.`销售顾问` ");
        sql.append(" 	UNION ALL ");
        sql.append(" 	SELECT  ");
        sql.append(" 		a.销售顾问, ");
        sql.append(" 		0 `首次试驾数`, ");
        sql.append(" 		0 `报价数`, ");
        sql.append(" 		SUM(IF(a.`是否订单`='是',1,0))+SUM(IF(a.`是否订单`='退订',-1,0)) `订单数`, ");
        sql.append(" 		SUM(IF(a.`是否交车`='是',1,0)) `交车数` ");
        sql.append(" 	FROM `顾客进店记录表` a  ");
        sql.append(" 	LEFT JOIN `顾客记录汇总` cust ON a.`顾客编号` = cust.`顾客编号` ");
        sql.append(" 	LEFT JOIN `lsh车型` car ON a.`意向车型A` = car.`车型编号` ");
        sql.append(" 	WHERE a.`进店日期` >=   :startDate  AND a.`进店日期` <=   :endDate  ");
        sql.append(" 	      AND a.许可 =  :permit  ");
        sql.append(" 	      AND cust.`顾客姓名`!='' ");
        sql.append(" 	      AND cust.`电话号码`!='' ");
        sql.append(" 	      AND car.`车型`!='' ");
        sql.append(" 	GROUP BY a.`销售顾问` ");
        sql.append(" ) aa ");
        if ("1".equals(groupType)) {
            sql.append(" GROUP BY 销售顾问 ");
        }
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("startDate", startDate);
//        params.put("startTime", startDate + " 00:00:01");
        params.put("endDate", endDate);
//        params.put("endTime", endDate + " 23:59:59");
        return getList(sql.toString(), params);
    }

    public List getWeekClueNum (String permit, String nowDate, String startDate, String endDate, String groupType, int day, int days) {
        StringBuffer sql = new StringBuffer();
        if (!"2".equals(groupType)) {
            sql.append(" SELECT a.渠道, a.销售顾问, SUM(a.线索数)  线索数 ");
            sql.append(" FROM ( ");
            sql.append(" 	SELECT ");
            sql.append(" 		a.`渠道` `渠道`, ");
            sql.append(" 		a.`所属销售顾问` 销售顾问, ");
            sql.append(" 		ROUND(IFNULL(COUNT(1),0) * :day / :days) `线索数` ");
            sql.append(" 	FROM `lsh导入名单` a ");
            sql.append(" 	WHERE a.`许可` = :permit AND a.`是否线索` = '是' AND a.`是否有效线索` = '是' AND a.所属销售顾问 IS NOT NULL ");
            sql.append(" 	AND a.`线索创建日期`>= date_sub( date_sub( date_format(:startDate, '%Y-%m-%d'), INTERVAL extract(DAY FROM :startDate) - 1 DAY ), INTERVAL 2 MONTH ) ");
            sql.append(" 	AND a.`线索创建日期`<= :nowDate ");
            sql.append("    AND a.`车型编号` IS NOT NULL ");
            sql.append(" 	AND NOT EXISTS( ");
            sql.append(" 		SELECT c.`电话号码` FROM `顾客记录汇总` c ");
            sql.append(" 		WHERE ( ");
            sql.append(" 			c.`顾客状态` <> '战败' OR ISNULL(c.`顾客状态`) ");
            sql.append(" 			OR (c.`顾客状态`='战败' AND a.`线索创建日期`> IFNULL(c.战败日期,'1970-01-01')) ");
            sql.append(" 		) AND c.`许可` = a.`许可` AND a.`电话号码` = c.`电话号码` ");
            sql.append(" 	) ");
            sql.append(" 	GROUP BY `渠道`,a.`所属销售顾问` ");
            sql.append(" 	UNION ALL ");
            sql.append(" 	SELECT ");
            sql.append(" 		a.`渠道` `渠道`, ");
            sql.append(" 		a.`所属销售顾问` 销售顾问, ");
            sql.append(" 		IFNULL(COUNT(1),0) `线索数` ");
            sql.append(" 	FROM `lsh导入名单` a ");
            sql.append(" 	WHERE a.`许可` = :permit AND a.`是否线索` = '是' AND a.`是否有效线索` = '是' AND a.所属销售顾问 IS NOT NULL ");
            sql.append(" 	AND a.`线索创建日期`>= :startDate ");
            sql.append(" 	AND a.`线索创建日期`<= :endDate ");
            sql.append("    AND a.`车型编号` IS NOT NULL ");
            sql.append(" 	AND NOT EXISTS( ");
            sql.append(" 		SELECT c.`电话号码` FROM `顾客记录汇总` c ");
            sql.append(" 		WHERE ( ");
            sql.append(" 			c.`顾客状态` <> '战败' OR ISNULL(c.`顾客状态`) ");
            sql.append(" 			OR (c.`顾客状态`='战败' AND a.`线索创建日期`> IFNULL(c.战败日期,'1970-01-01')) ");
            sql.append(" 		) AND c.`许可` = a.`许可` AND a.`电话号码` = c.`电话号码` ");
            sql.append(" 	) ");
            sql.append(" 	GROUP BY `渠道`,a.`所属销售顾问` ");
            sql.append(" ) a ");
            sql.append(" WHERE a.渠道 IS NOT NULL ");
            sql.append(" GROUP BY a.渠道 ");
            if ("1".equals(groupType)) {
                sql.append(" , a.销售顾问 ");
            }
        } else {
            sql.append(" SELECT a.渠道, a.CDO, SUM(a.线索数)  线索数 ");
            sql.append(" FROM ( ");
            sql.append(" 	SELECT ");
            sql.append(" 		a.`渠道` `渠道`, ");
            sql.append(" 		a.`所属CDO` CDO, ");
            sql.append(" 		ROUND(IFNULL(COUNT(1),0) * :day / :days) `线索数` ");
            sql.append(" 	FROM `lsh导入名单` a ");
            sql.append(" 	WHERE a.`许可` = :permit AND a.`是否线索` = '是' AND a.`是否有效线索` = '是' AND a.所属CDO IS NOT NULL ");
            sql.append(" 	AND a.`线索创建日期`>= date_sub( date_sub( date_format(:startDate, '%Y-%m-%d'), INTERVAL extract(DAY FROM :startDate) - 1 DAY ), INTERVAL 2 MONTH ) ");
            sql.append(" 	AND a.`线索创建日期`<= :nowDate ");
            sql.append("    AND a.`车型编号` IS NOT NULL ");
            sql.append(" 	AND NOT EXISTS( ");
            sql.append(" 		SELECT c.`电话号码` FROM `顾客记录汇总` c ");
            sql.append(" 		WHERE ( ");
            sql.append(" 			c.`顾客状态` <> '战败' OR ISNULL(c.`顾客状态`) ");
            sql.append(" 			OR (c.`顾客状态`='战败' AND a.`线索创建日期`> IFNULL(c.战败日期,'1970-01-01')) ");
            sql.append(" 		) AND c.`许可` = a.`许可` AND a.`电话号码` = c.`电话号码` ");
            sql.append(" 	) ");
            sql.append(" 	GROUP BY `渠道`,a.`所属CDO` ");
            sql.append(" 	UNION ALL ");
            sql.append(" 	SELECT ");
            sql.append(" 		a.`渠道` `渠道`, ");
            sql.append(" 		a.`所属CDO` CDO, ");
            sql.append(" 		IFNULL(COUNT(1),0) `线索数` ");
            sql.append(" 	FROM `lsh导入名单` a ");
            sql.append(" 	WHERE a.`许可` = :permit AND a.`是否线索` = '是' AND a.`是否有效线索` = '是' AND a.所属CDO IS NOT NULL ");
            sql.append(" 	AND a.`线索创建日期`>= :startDate ");
            sql.append(" 	AND a.`线索创建日期`<= :endDate ");
            sql.append("    AND a.`车型编号` IS NOT NULL ");
            sql.append(" 	AND NOT EXISTS( ");
            sql.append(" 		SELECT c.`电话号码` FROM `顾客记录汇总` c ");
            sql.append(" 		WHERE ( ");
            sql.append(" 			c.`顾客状态` <> '战败' OR ISNULL(c.`顾客状态`) ");
            sql.append(" 			OR (c.`顾客状态`='战败' AND a.`线索创建日期`> IFNULL(c.战败日期,'1970-01-01')) ");
            sql.append(" 		) AND c.`许可` = a.`许可` AND a.`电话号码` = c.`电话号码` ");
            sql.append(" 	) ");
            sql.append(" 	GROUP BY `渠道`,a.`所属CDO` ");
            sql.append(" ) a ");
            sql.append(" WHERE a.渠道 IS NOT NULL ");
            sql.append(" GROUP BY a.渠道, a.CDO ");
        }
        Map params = new HashMap();
        params.put("permit", permit);
        params.put("nowDate", nowDate);
        params.put("startDate", startDate);
        params.put("endDate", endDate);
        params.put("day", day);
        params.put("days", days);
        return getList(sql.toString(), params);
    }
}
